﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Diagnostics;
using CNKI.Graduate.Framework.Log;
using CNKI.Graduate.Framework.Ioc;

namespace CNKI.Graduate.Framework.Db.Mysql
{
    [ServiceImplementation]
    public class Database : IDatabase
    {
        private readonly string _connString;// = "server=localhost;user id=root;password=lht621921;database=nanjing";
        public Database()
        {
            _connString = ConfigurationManager.ConnectionStrings["graduateDb_Mysql"].ConnectionString;
        }


        public string GetSqlParamVar(string name)
        {
            return string.Format("@{0}", name);
        }


        #region private

        private static void PrepareCommand(MySqlCommand cmd, SqlParamsData[] sqlParams)
        {
            cmd.Parameters.Clear();
            if (sqlParams == null || sqlParams.Length == 0)
                return;
            foreach (SqlParamsData var in sqlParams)
            {
                MySqlParameter para = cmd.CreateParameter();
                para.DbType = var.ParaType;
                para.Direction = var.ParaDirection;
                para.ParameterName = var.ParaName;
                para.Value = var.ParaValue;

                cmd.Parameters.Add(para);
            }

        }

      
        private SqlParamsData[] GetParameterValues(DataColumnCollection columns, DataRow dr)
        {
            List<SqlParamsData> list = new List<SqlParamsData>(columns.Count);
            foreach (DataColumn col in columns)
            {
                list.Add(new SqlParamsData(GetSqlParamVar(col.ColumnName), dr[col.ColumnName], GetSqlDbType(col.DataType),
                                            ParameterDirection.Input));
            }
            return list.ToArray();
        }
        private DbType GetSqlDbType(Type dataType)
        {
            if (dataType == typeof(string)
                || dataType == typeof(char))
                return DbType.String;

            if (dataType == typeof(DateTime))
                return DbType.DateTime;

            if (dataType == typeof(byte)
                || dataType == typeof(sbyte)
                || (dataType.IsPrimitive && dataType.Name.Contains("Int")))
                return DbType.Int32;

            if (dataType == typeof(float))
            {
                return DbType.Single;
            }
            if (dataType == typeof(double))
            {
                return DbType.Double;
            }
            return DbType.String;
        }

        private string GetInsertSqlWithParameter(DataColumnCollection columns, string tableName)
        {
            StringBuilder sbCol = new StringBuilder();
            StringBuilder sbValue = new StringBuilder();
            sbCol.AppendFormat(" insert into {0}(", tableName);
            int i = 0;
            foreach (DataColumn col in columns)
            {
                if (i == 0)
                {
                    sbCol.AppendFormat("{0}", col.ColumnName);
                    sbValue.Append(GetSqlParamVar(col.ColumnName));
                }
                else
                {
                    sbCol.AppendFormat(",{0}", col.ColumnName);
                    sbValue.AppendFormat(",{0}", GetSqlParamVar(col.ColumnName));
                }
                i++;
            }
            sbCol.Append(") values(");
            sbCol.AppendFormat("{0})", sbValue.ToString());

            return sbCol.ToString();
        }
        private string GetUpdateSqlWithParameter(DataColumnCollection columns, string tableName, string[] primaryKeyColumns)
        {
            StringBuilder sbCol = new StringBuilder();
            sbCol.AppendFormat(" update {0} set ", tableName);
            int i = 0;
            List<string> listPrimary = new List<string>(primaryKeyColumns);
            foreach (DataColumn col in columns)
            {
                if (listPrimary.Contains(col.ColumnName))
                {
                    continue;
                }

                if (i == 0)
                {
                    sbCol.AppendFormat("{0}={1}", col.ColumnName, GetSqlParamVar(col.ColumnName));
                }
                else
                {
                    sbCol.AppendFormat(",{0}={1}", col.ColumnName, GetSqlParamVar(col.ColumnName));
                }
                i++;
            }
            i = 0;
            foreach (string col in listPrimary)
            {
                if (i == 0)
                {
                    sbCol.AppendFormat(" where {0}={1} ", col, GetSqlParamVar(col));
                }
                else
                {
                    sbCol.AppendFormat(" and {0}={1} ", col, GetSqlParamVar(col));
                }
                i++;
            }

            return sbCol.ToString();
        }

        #endregion
        public DataTable ExecuteDataTable(string commandText)
        {
            return ExecuteDataTable(commandText, null);
        }

        public DataTable ExecuteDataTable(string commandText, SqlParamsData[] sqlParams)
        {
            DataTable dt = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(_connString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(commandText, conn))
                    {
                        PrepareCommand(cmd, sqlParams);
                        using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                    }
                }
                catch (Exception ex)
                {
                    LogHandle.Debug(ex);
                }
            }
            return dt;
        }

        public bool ExecuteMutiNonQuery(string[] sqlList)
        {
            if (sqlList == null || sqlList.Length == 0)
                return false;
            Dictionary<string, SqlParamsData[]> dict = new Dictionary<string, SqlParamsData[]>();
            foreach (string s in sqlList)
            {
                dict.Add(s, null);
            }
            return ExecuteMutiNonQuery(dict);
        }

        public bool ExecuteMutiNonQuery(IDictionary<string, SqlParamsData[]> sqlPair)
        {
            if (sqlPair == null || sqlPair.Count == 0)
                return false;
            using (MySqlConnection sqlcon = new MySqlConnection(_connString))
            {
                MySqlTransaction tran = null;
                try
                {
                    sqlcon.Open();
                    tran = sqlcon.BeginTransaction();
                    foreach (KeyValuePair<string, SqlParamsData[]> valuePair in sqlPair)
                    {
                        using (MySqlCommand cmd = new MySqlCommand(valuePair.Key, sqlcon))
                        {
                            PrepareCommand(cmd, valuePair.Value);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    if (tran != null)
                    {
                        tran.Rollback();

                    }
                    LogHandle.Error(ex.Message, ex);
                }
                finally
                {
                    if (tran != null)
                    {
                        tran.Dispose();
                    }
                }
            }

            return false;
        }

        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, null);
        }

        public int ExecuteNonQuery(string commandText, SqlParamsData[] sqlParams)
        {
            using (MySqlConnection sqlcon = new MySqlConnection(_connString))
            {
                try
                {
                    sqlcon.Open();
                    using (MySqlCommand cmd = new MySqlCommand(commandText, sqlcon))
                    {
                        PrepareCommand(cmd, sqlParams);
                        return cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    LogHandle.Debug(ex);
                }
            }
            return -1;
        }

        public IDataReader ExecuteReader(string commandText)
        {
            return ExecuteReader(commandText,null);
        }

        public IDataReader ExecuteReader(string commandText, SqlParamsData[] sqlParams)
        {
            using (MySqlConnection sqlcon = new MySqlConnection(_connString))
            {
                try
                {
                    sqlcon.Open();
                    using (MySqlCommand cmd = new MySqlCommand(commandText, sqlcon))
                    {
                        PrepareCommand(cmd, sqlParams);
                        return cmd.ExecuteReader();
                    }
                }
                catch (Exception ex)
                {
                    LogHandle.Debug(ex);
                }
            }
            return null;
        }

        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(commandText, null);
        }

        public object ExecuteScalar(string commandText, SqlParamsData[] sqlParams)
        {
            using (MySqlConnection sqlcon = new MySqlConnection(_connString))
            {
                try
                {
                    sqlcon.Open();
                    using (MySqlCommand cmd = new MySqlCommand(commandText, sqlcon))
                    {
                        PrepareCommand(cmd, sqlParams);
                        return cmd.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    LogHandle.Debug(ex);
                }
            }
            return null;
        }

        public bool SaveDataTableContent(DataTable dataTable)
        {
            return SaveDataTableContent(dataTable, dataTable.TableName);
        }

        public bool SaveDataTableContent(DataTable dataTable, string tableName)
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                LogHandle.Warn("SaveDataTableContent:Table is null or no rows");
                return false;
            }
            dataTable.TableName = tableName;

            using (MySqlConnection sqlcon = new MySqlConnection(_connString))
            {
                MySqlTransaction tran = null;
                try
                {
                    string sql = GetInsertSqlWithParameter(dataTable.Columns, tableName);
                    sqlcon.Open();
                    tran = sqlcon.BeginTransaction();
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        SqlParamsData[] paras = GetParameterValues(dataTable.Columns, dr);

                        MySqlCommand cmd = new MySqlCommand(sql, sqlcon);
                        PrepareCommand(cmd, paras);
                        cmd.Transaction = tran;
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    if (tran != null)
                    {
                        tran.Rollback();

                    }
                    LogHandle.Error(ex.Message, ex);
                }
                finally
                {
                    if (tran != null)
                    {
                        tran.Dispose();
                    }
                }
            }

            return false;
        }

        public bool UpdateDataTableContent(DataTable dataTable, string[] primaryKeyColumns)
        {
            return UpdateDataTableContent(dataTable, dataTable.TableName, primaryKeyColumns);
        }

        public bool UpdateDataTableContent(DataTable dataTable, string tableName, string[] primaryKeyColumns)
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                LogHandle.Warn("UpdateDataTableContent:Table is null or no rows");
                return false;
            }
            dataTable.TableName = tableName;

            using (MySqlConnection sqlcon = new MySqlConnection(_connString))
            {
                MySqlTransaction tran = null;
                try
                {
                    string sql = GetUpdateSqlWithParameter(dataTable.Columns, tableName, primaryKeyColumns);
                    sqlcon.Open();
                    tran = sqlcon.BeginTransaction();
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        SqlParamsData[] paras = GetParameterValues(dataTable.Columns, dr);

                        MySqlCommand cmd = new MySqlCommand(sql, sqlcon);
                        PrepareCommand(cmd, paras);
                        cmd.Transaction = tran;
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    if (tran != null)
                    {
                        tran.Rollback();

                    }
                    LogHandle.Error(ex.Message, ex);
                }
                finally
                {
                    if (tran != null)
                    {
                        tran.Dispose();
                    }
                }
            }

            return false;
        }
    }
}
